package com.tsinjie.metadata.database;

import com.tsinjie.metadata.database.comment.factory.TableCommentFactory;
import com.tsinjie.metadata.database.model.Column;
import com.tsinjie.metadata.database.model.Database;
import com.tsinjie.metadata.database.model.ExportColumn;
import com.tsinjie.metadata.database.model.ImportColumn;
import com.tsinjie.metadata.database.model.Table;
import com.tsinjie.metadata.exception.BuildException;
import org.apache.commons.beanutils.BeanUtils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * Created by LiuXunming on 2015-10-13.
 * 数据库连接 Database 构建类
 */
public class ConnectionDatabaseBuilder extends AbstractDatabaseBuilder<Connection> {
    /**
     * 元数据
     */
    protected DatabaseMetaData dbMetaData = null;

    public ConnectionDatabaseBuilder(Connection connection) throws SQLException {
        super(connection);
        this.dbMetaData = connection.getMetaData();
    }

    /**
     * 构建完成后 关闭数据库连接
     */
    @Override
    protected void afterProcess() {
        try {
            source.close();
        } catch (SQLException e) {
            throw new BuildException(e);
        }
    }

    /**
     * 构建Database对象
     * {@link com.tsinjie.metadata.database.model.Database}
     *
     * @return Database
     * @throws Exception
     */
    public Database database() throws Exception {
        Database database = new Database();
        database.setUrl(dbMetaData.getURL());
        database.setUsername(dbMetaData.getUserName());
        database.setReadonly(dbMetaData.isReadOnly());
        database.setDatabaseProductName(dbMetaData.getDatabaseProductName());
        database.setDatabaseProductVersion(dbMetaData.getDatabaseProductVersion());
        database.setDriverName(dbMetaData.getDriverName());
        database.setDriverVersion(dbMetaData.getDriverVersion());
        List<String> schemas = new ArrayList<String>();
        ResultSet rs = dbMetaData.getSchemas();
        while (rs.next()) {
            schemas.add(rs.getString("TABLE_SCHEM"));
        }
        database.setSchemas(schemas);
        rs.close();
        return database;
    }

    /**
     * 构建表信息
     * {@link com.tsinjie.metadata.database.model.Table}
     *
     * @param database
     * @throws Exception
     */
    @Override
    public void tables(Database database) throws Exception {
        DatabaseMetaData dbmd = source.getMetaData();
        ResultSet rs = dbmd.getTables(null, null, null, new String[]{"table"});
        Table table = null;
        while (rs.next()) {
            String schemaName = rs.getString(1);
            String tableName = rs.getString(3);
            /*获取表注释*/
            String comment = TableCommentFactory.getComment(source, tableName, schemaName);
            table = new Table();
            table.setTableName(tableName);
            table.setComment(comment);
            database.addTable(table);
        }
        rs.close();
    }

    /**
     * 构建字段信息
     * {@link com.tsinjie.metadata.database.model.Column}
     *
     * @param tables
     * @throws Exception
     */
    @Override
    public void fields(List<Table> tables) throws Exception {
        for (Table table : tables) {
            ResultSet rs = source.getMetaData().getColumns(null, null, table.getTableName(), null);
            Column column = null;
            while (rs.next()) {
                column = new Column();
                column.setColumnName(rs.getString("COLUMN_NAME"));
                column.setComment(rs.getString("REMARKS"));
                column.setType(rs.getInt("DATA_TYPE"));
                column.setLength(rs.getInt("COLUMN_SIZE"));
                column.setNullable(rs.getInt("NULLABLE") == 0 ? false : true);
                column.setDefaultValue(rs.getString("COLUMN_DEF"));
                table.addColumn(column);
            }
            rs.close();
        }
    }

    /**
     * 构建主键信息
     * {@link com.tsinjie.metadata.database.model.Column}
     *
     * @param tables
     * @throws Exception
     */
    @Override
    public void primaryKeys(List<Table> tables) throws Exception {
        for (Table table : tables) {
            List<Column> columns = table.getColumns();
            ResultSet rs = source.getMetaData().getPrimaryKeys(null, null, table.getTableName());
            while (rs.next()) {
                String primaryKeyName = rs.getString(4);
                for (Iterator<Column> it = columns.iterator(); it.hasNext(); ) {
                    /*匹配外键列表，填充外键信息*/
                    Column column = it.next();
                    if (primaryKeyName.equals(column.getColumnName())) {
                        table.addPrimaryKey(column);
                    }
                }
            }
            if (table.getPrimaryKeys() == null) {
                throw new RuntimeException("table " + table.getTableName() + " has no Primary Key.");
            }
            rs.close();
        }
    }

    /**
     * 构建外键信息
     * {@link com.tsinjie.metadata.database.model.Column}
     *
     * @param tables
     * @throws Exception
     */
    @Override
    public void importFields(List<Table> tables) throws Exception {
        for (Table table : tables) {
            List<Column> columns = table.getColumns();
            ResultSet rs = source.getMetaData().getImportedKeys(null, null, table.getTableName());
            ImportColumn importColumn = null;
            while (rs.next()) {
                importColumn = new ImportColumn();
                String fkColumnName = rs.getString("FKCOLUMN_NAME");
                String pkTableName = rs.getString("PKTABLE_NAME");
                String pkColumnName = rs.getString("PKCOLUMN_NAME");
                importColumn.setColumnName(fkColumnName);
                importColumn.setExportTableName(pkTableName);
                importColumn.setExportColumnName(pkColumnName);
                for (Iterator<Column> it = columns.iterator(); it.hasNext(); ) {
                    /*匹配外键列表，填充外键信息*/
                    Column column = it.next();
                    if (importColumn.equals(column)) {
                        BeanUtils.copyProperties(importColumn, column);
                    }
                }
                table.addImportColumn(importColumn);
            }
            rs.close();
        }
    }

    /**
     * 避免某字段同时为主键和外键，不在生成主外键的循环中做移除操作
     * 清理表字段（主键和外键）
     *
     * @param tables
     * @throws Exception
     */
    @Override
    public void clearUp(List<Table> tables) throws Exception {
        for (Iterator<Table> it = tables.iterator(); it.hasNext(); ) {
            Table table = it.next();
            List<Column> columns = table.getColumns();
            /*将对应的主键从字段列表中移除*/
            List<Column> primaryKeys = table.getPrimaryKeys();
            if (primaryKeys != null) {
                for (Column pk : primaryKeys) {
                    columns.remove(pk);
                }
            }
            /*将对应的外键从字段列表中移除*/
            List<ImportColumn> importColumns = table.getImportColumns();
            if (importColumns != null) {
                for (Column fk : importColumns) {
                    columns.remove(fk);
                }
            }
        }
    }

    /**
     * 构建被引用（Export）键信息
     * {@link com.tsinjie.metadata.database.model.Column}
     *
     * @param tables
     * @throws Exception
     */
    @Override
    public void exportFields(List<Table> tables) throws Exception {
        for (Table table : tables) {
            ResultSet rs = source.getMetaData().getExportedKeys(null, null, table.getTableName());
            ExportColumn exportColumn = null;
            while (rs.next()) {
                exportColumn = new ExportColumn();
                String pkColumnName = rs.getString("PKCOLUMN_NAME");
                String fkTableName = rs.getString("FKTABLE_NAME");
                String fkColumnName = rs.getString("FKCOLUMN_NAME");
                exportColumn.setColumnName(pkColumnName);
                exportColumn.setImportTableName(fkTableName);
                exportColumn.setImportColumnName(fkColumnName);
                Column source = table.getPrimaryKey(exportColumn.getColumnName());
                if (source == null) {
                    source = table.getImportColumn(exportColumn.getColumnName());
                }
                if (source == null) {
                    source = table.getColumn(exportColumn.getColumnName());
                }
                BeanUtils.copyProperties(exportColumn, source);
                table.addExportColumn(exportColumn);
            }
        }
    }
}
